import openpyxl
import pymysql

# 建立mysql链接
db = pymysql.connect(host='10.2.3.5',       # 10.28.10.2
                     port=3306,
                     user='root',           # dev dskj
                     passwd='sirun@2018',   # dev Dskj!2019
                     db='bss-gq',
                     charset='utf8')
cursor = db.cursor()  # 使用cursor()方法获取操作游标
# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook("E:\\件\\DS\\x9e\\车型导入葵花宝典\\AM8_远程监控20200521.xlsx")
# 获取workbook中所有的表格
sheets = wb.sheetnames

vehicle_ser = "7"
# vehicle_model_id = "20"
vehicle_model_id_list = {20, 21, 22, 30, 31, 33, 34, 35}
number = 1
special_mc_code = [22, 23, 24, 25, 35, 40, 47, 48, 63, 80]
example_mc_code = 123
item = {}
for sheet in sheets:
    if sheet == "远程监控":
        ws = wb.active  # 当前活跃的表单
        for vehicle_model_id in vehicle_model_id_list:
            for i in range(2, 104):

                item['AM8'] = ws.cell(row=i, column=11).value  # AM8
                if item['AM8'] in '×':
                    print(str(i) + 'x')
                    continue

                item['mc_code'] = ws.cell(row=i, column=1).value  # 信号编码

                if special_mc_code.count(item['mc_code']) != 0:
                    select_str = "select * from tab_mc where `vehicle_model_id` = " + str(example_mc_code) \
                                 + " and `mc_code` = " + str(item['mc_code'])
                    count = cursor.execute(select_str)

                    result = cursor.fetchall()
                    listResult = []
                    for subResult in list(result):

                        subResult = list(subResult)
                        subResult[0] = vehicle_ser + str(vehicle_model_id) + "%03d" % number
                        subResult[1] = vehicle_model_id
                        del subResult[14]
                        del subResult[13]
                        del subResult[9]
                        del subResult[8]

                        listResult.append(tuple(subResult))
                        number = number + 1

                        print(subResult)

                    insertItem = tuple(listResult)

                    into = "insert into `tab_mc` (`mc_id`, `vehicle_model_id`, `ecu_code`, `mc_code`, " \
                           "`value_len`,`max_val`,`min_val`,`message`,`mc_name`,`signal_value`,`company`) " \
                           "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                    cursor.executemany(into, insertItem)
                    db.commit()

                    continue

                item['mc_id'] = vehicle_ser + str(vehicle_model_id) + "%03d" % number
                item['vehicle_model_id'] = vehicle_model_id
                item['ecu_code'] = ws.cell(row=i, column=2).value  # 节点
                item['value_len'] = ws.cell(row=i, column=6).value  # 信号长度
                item['max_val'] = ws.cell(row=i, column=10).value  # 最大值
                item['min_val'] = ws.cell(row=i, column=9).value  # 最小值
                item['message'] = ws.cell(row=i, column=5).value.replace(" ", "")  # 描述
                item['company'] = ws.cell(row=i, column=8).value  # 单位

                if not ws.cell(row=i, column=4).value:
                    # item['mc_name'] = ws.cell(row=i, column=3).value
                    mc_name = ws.cell(row=i, column=3).value
                    splitname = mc_name.split("_")
                    result = ""
                    if len(splitname) > 0:
                        splitname[0] = splitname[0].lower()
                    result = result.join(splitname)
                    item['mc_name'] = result
                else:
                    item['mc_name'] = ws.cell(row=i, column=4).value  # 信号名

                item['signal_value'] = ws.cell(row=i, column=7).value.replace(" ", "")  # 信号值

                number = number + 1

                print(item)
                into = "insert into `tab_mc` (`mc_id`, `vehicle_model_id`, `ecu_code`, `mc_code`, `value_len`," \
                       "`max_val`,`min_val`,`message`,`mc_name`,`signal_value`,`company`) " \
                       "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                values = (item['mc_id'], item['vehicle_model_id'], item['ecu_code'], item['mc_code'], item['value_len'],
                          item['max_val'], item['min_val'], item['message'], item['mc_name'], item['signal_value'],
                          item['company'])
                cursor.execute(into, values)
                db.commit()

cursor.close()
db.close()
